if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[GetAllSubmittedCOIs]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[GetAllSubmittedCOIs]
GO

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO


/******************************************************************
* Name:			GetAllSubmittedCOIs
* Purpose:		Retrieves all the submitted COIs 
*
* PARAMETERS
* Name    		Description					
* -------------  	-------------------------------------------
* 
******************************************************************/

CREATE Procedure [dbo].[GetAllSubmittedCOIs] 
AS
BEGIN

	SET NOCOUNT ON

	Select	Id,
		AdmPersonId,
		FwkDomainUserId,
		LastNotified,
		LastModified,
		DateSubmitted,
		DateReviewed,
		HasOutsideActivities,
		HasEquityInterests,
		HasFamilyAssociations,
		Royalty,
		OtherActivity,
		PolicyViolation,
		AggregateEquity,
		Status,
		RefCode.RefMeaning as StatusMeaning, 
		IsNull(DocumentId, 0) as DocumentId
	FROM ConflictOfInterest COI
		INNER JOIN RefCode ON COI.Status = RefCode.RefCode
	WHERE Status = 'COISTATUS_SUBMITTED'
	--and policyviolation = 1
	--and COI.datesubmitted > '2007-12-03 11:51:29.733' and coi.datesubmitted <= '2007-12-10 13:30:29.653'
	ORDER BY Id Asc
	
END
GO
